{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sys\n",
    "import seaborn as sns\n",
    "from time import sleep\n",
    "import matplotlib.pyplot as plt\n",
    "# on_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\ccf_online_stage1_train\\ccf_online_stage1_train.csv')\n",
    "off_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\ccf_offline_stage1_train\\ccf_offline_stage1_train.csv')\n",
    "# oftid_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\off_train_sameUser_id_test.csv')\n",
    "test=pd.read_csv(r'D:\\Data\\TCForNewComer\\ccf_offline_stage1_test_revised.csv')\n",
    "\n",
    "off_train=off_train.sort_values(by=['User_id'])\n",
    "off_train.index=np.arange(0,len(off_train),1)\n",
    "test['Date_received']=test['Date_received'].astype(np.int64)\n",
    "test['Date_received']=test['Date_received'].apply(lambda x: str(x))\n",
    "test['discount_rate']=test['Discount_rate'].copy()\n",
    "test.index=np.arange(0,len(test),1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "indexs=off_train[(off_train['Coupon_id']=='null')&(off_train['Date']!='null')].index\n",
    "ordinary=off_train.iloc[indexs]\n",
    "off_train.drop(indexs,axis=0,inplace=True)\n",
    "ordinary.index=np.arange(0,len(ordinary),1)\n",
    "off_train.index=np.arange(0,len(off_train),1)\n",
    "ordinary['month']=ordinary['Date'].apply(lambda x: int(str(x)[5])).copy()\n",
    "off_train['month']=off_train['Date_received'].apply(lambda x: int(str(x)[5])).copy()\n",
    "\n",
    "ftest=off_train[off_train['month']==6].copy()\n",
    "ftest.drop(['Date','month'],axis=1,inplace=True)\n",
    "ftest['discount_rate']=ftest['Discount_rate']\n",
    "ftest.index=np.arange(0,len(ftest),1)\n",
    "# ftesty=off_train[off_train['month']==6]['Date'].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ftest['Date_received']=ftest['Date_received'].apply(lambda x: str(x))\n",
    "\n",
    "p_train=ftest[off_train['Date']!='null'].copy()#正样本\n",
    "n_train=ftest[off_train['Date']=='null'].copy()#负样本\n",
    "p_train.index=np.arange(0,len(p_train),1)\n",
    "n_train.index=np.arange(0,len(n_train),1)\n",
    "#转换为时间格式\n",
    "p_train['Date_received_copy']=p_train['Date_received'].copy()\n",
    "p_train['Date_received_copy']=pd.to_datetime(p_train['Date_received_copy'])\n",
    "p_train['Date']=pd.to_datetime(p_train['Date'])\n",
    "\n",
    "p_train['date']=(p_train['Date']-p_train['Date_received_copy']).astype('timedelta64[D]')\n",
    "p_train.loc[p_train[p_train['date']<=15].index,'date']=1\n",
    "p_train.loc[p_train[p_train['date']>15].index,'date']=0\n",
    "p_train['Date']=p_train['date'].copy()\n",
    "p_train.drop(['date','Date_received_copy'],axis=1,inplace=True)\n",
    "p_train['Date']=p_train['Date'].astype(np.int64)\n",
    "n_train['Date']=0\n",
    "ftest=pd.concat([p_train,n_train],axis=0)\n",
    "ftest=ftest.sort_values(by=['User_id'])\n",
    "ftest.index=np.arange(0,len(ftest),1)\n",
    "\n",
    "ftesty=pd.DataFrame()\n",
    "ftesty['Date']=ftest['Date']\n",
    "ftesty['Coupon_id']=ftest['Coupon_id']\n",
    "ftesty.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\ftesty.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ". 10 %;.. 20 %;... 30 %;.... 40 %;..... 50 %;...... 60 %;....... 70 %;........ 80 %;......... 90 %;"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:50: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "off_train['ord_buy']=0\n",
    "off_train['same_merc']=0\n",
    "test['ord_buy']=0\n",
    "test['same_merc']=0\n",
    "i=0\n",
    "j=10\n",
    "length=len(set(off_train['User_id'].values))\n",
    "for uid in set(off_train['User_id'].values):\n",
    "    rate=int(i*100/length)#看进度\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%',end=';')\n",
    "        j=j+10\n",
    "        \n",
    "    tt=off_train[off_train['User_id']==uid]\n",
    "    tto=ordinary[ordinary['User_id']==uid]\n",
    "    if len(tto)!=0:\n",
    "        for mid in set(tt['Merchant_id'].values):\n",
    "            tat=tt[tt['Merchant_id']==mid]\n",
    "            tato=tto[tto['Merchant_id']==mid]\n",
    "            if len(tato)!=0:\n",
    "                off_train.loc[tat.index,'ord_buy']=len(tato)\n",
    "    for cid in set(tt['Coupon_id'].values):\n",
    "        tct=tt[tt['Coupon_id']==cid]\n",
    "        for month in set(tct['month'].values):\n",
    "            tctm=tct[tct['month']==month]\n",
    "            off_train.loc[tctm.index,'same_merc']=len(tctm)\n",
    "    i+=1\n",
    "    \n",
    "for uid in set(test['User_id'].values):\n",
    "    if uid in off_train['User_id'].values:\n",
    "        tt=test[test['User_id']==uid]\n",
    "        for mid in set(tt['Merchant_id'].values):\n",
    "            if mid in off_train[off_train['User_id']==uid]['Merchant_id'].values:\n",
    "                test.loc[tt[tt['Merchant_id']==mid].index,'ord_buy']=off_train[(off_train['User_id']==uid)&(off_train['Merchant_id']==mid)]['ord_buy'].max()\n",
    "    for cid in set(test[test['User_id']==uid]['Coupon_id'].values):\n",
    "        tct=test[(test['Coupon_id']==cid)&(test['User_id']==uid)]\n",
    "        test.loc[tct.index,'same_merc']=len(tct)\n",
    "        \n",
    "for uid in set(ftest['User_id'].values):\n",
    "    if uid in off_train['User_id'].values:\n",
    "        tt=ftest[ftest['User_id']==uid]\n",
    "        for mid in set(tt['Merchant_id'].values):\n",
    "            if mid in off_train[off_train['User_id']==uid]['Merchant_id'].values:\n",
    "                ftest.loc[tt[tt['Merchant_id']==mid].index,'ord_buy']=off_train[(off_train['User_id']==uid)&(off_train['Merchant_id']==mid)]['ord_buy'].max()\n",
    "    for cid in set(ftest[ftest['User_id']==uid]['Coupon_id'].values):\n",
    "        tct=ftest[(ftest['Coupon_id']==cid)&(ftest['User_id']==uid)]\n",
    "        ftest.loc[tct.index,'same_merc']=len(tct)\n",
    "\n",
    "# ordinary.drop(['month'],axis=1,inplace=True)\n",
    "# off_train.drop(['month'],axis=1,inplace=True)\n",
    "off_train.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_off_train.csv',index=False)\n",
    "test.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_test.csv',index=False)\n",
    "ftest.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_ftest.csv',index=False)\n",
    "ftesty.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\ftesty.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "off_train.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_off_train.csv',index=False)\n",
    "test.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_test.csv',index=False)\n",
    "ftest.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_ftest.csv',index=False)\n",
    "ftesty.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\ftesty.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "off_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\off_train.csv')\n",
    "test=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\test.csv')\n",
    "ftest=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\ftest.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ". 10 %;.. 20 %;... 30 %;.... 40 %;..... 50 %;...... 60 %;....... 70 %;........ 80 %;......... 90 %;"
     ]
    }
   ],
   "source": [
    "off_train['Coupon_month_num']=0             #每月领取张数\n",
    "# off_train['Coupon_month_nouse_num']=0       #每月领取没有使用张数\n",
    "# off_train['Coupon_month_use_num']=0         #每月使用张数\n",
    "# off_train['Coupon_month_use_prob']=0        #每月使用概率\n",
    "off_train['month']=off_train['Date_received'].apply(lambda x: int(str(x)[5])).copy()\n",
    "i=0\n",
    "j=10\n",
    "length=len(set(off_train['User_id'].values))\n",
    "for uid in set(off_train['User_id'].values):\n",
    "    rate=int(i*100/length)#看进度\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%',end=';')\n",
    "        j=j+10\n",
    "    \n",
    "    tt=off_train[off_train['User_id']==uid]\n",
    "    for month in set(tt['month'].values):\n",
    "        tat=tt[tt['month']==month]\n",
    "        off_train.loc[tat.index,'Coupon_month_num']=len(tat)\n",
    "    i=i+1\n",
    "#         off_train.loc[tat.index,'Coupon_month_nouse_num']=len(tat[tat['Date']=='null'])\n",
    "#         off_train.loc[tat.index,'Coupon_month_use_num']=len(tat[tat['Date']!='null'])\n",
    "#         off_train.loc[tat.index,'Coupon_month_use_prob']=len(tat[tat['Date']=='null'])\n",
    "Test=[test,ftest]\n",
    "for data in Test:\n",
    "    data['Coupon_month_num']=0\n",
    "    for uid in set(data['User_id'].values):\n",
    "        tt=data[data['User_id']==uid]\n",
    "        data.loc[tt.index,'Coupon_month_num']=len(tt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#对正负样本进行赋值\n",
    "combine=[off_train,test,ftest]\n",
    "for data in combine:\n",
    "    data['Date_received']=data['Date_received'].apply(lambda x: str(x))\n",
    "\n",
    "p_train=off_train[off_train['Date']!='null'].copy()#正样本\n",
    "n_train=off_train[off_train['Date']=='null'].copy()#负样本\n",
    "p_train.index=np.arange(0,len(p_train),1)\n",
    "n_train.index=np.arange(0,len(n_train),1)\n",
    "#转换为时间格式\n",
    "p_train['Date_received_copy']=p_train['Date_received'].copy()\n",
    "p_train['Date_received_copy']=pd.to_datetime(p_train['Date_received_copy'])\n",
    "p_train['Date']=pd.to_datetime(p_train['Date'])\n",
    "\n",
    "p_train['date']=(p_train['Date']-p_train['Date_received_copy']).astype('timedelta64[D]')\n",
    "p_train.loc[p_train[p_train['date']<=15].index,'date']=1\n",
    "p_train.loc[p_train[p_train['date']>15].index,'date']=0\n",
    "p_train['Date']=p_train['date'].copy()\n",
    "p_train.drop(['date','Date_received_copy'],axis=1,inplace=True)\n",
    "p_train['Date']=p_train['Date'].astype(np.int64)\n",
    "#转换为星期几x.weekday()+1\n",
    "test['Date_received']=(test['Date_received'].apply(lambda x: int(x[6:]))).copy()\n",
    "ftest['Date_received']=(ftest['Date_received'].apply(lambda x: int(x[6:]))).copy()\n",
    "#正样本为1，负样本为0\n",
    "n_train['Date']=0\n",
    "off_train=pd.concat([p_train,n_train],axis=0)\n",
    "off_train['Date_received']=(off_train['Date_received'].apply(lambda x: int(x[6:]))).copy()\n",
    "off_train['Date_received']=off_train['Date_received'].astype(np.int64)\n",
    "\n",
    "off_train['discount_rate']=off_train['Discount_rate'].copy()#增加一列可以看出实际商品价格\n",
    "#将日期转化为多少号\n",
    "combine=[off_train,test,ftest]\n",
    "def Discount_rate(x):#转化函数\n",
    "    if x.startswith('0'):\n",
    "        return float(x)\n",
    "    else:\n",
    "        return float(x.split(':')[0])\n",
    "for data in combine:\n",
    "    data.index=np.arange(0,len(data),1)\n",
    "    data['discount_rate']=data['discount_rate'].apply(Discount_rate)\n",
    "    \n",
    "off_train=off_train.sort_values(by=['User_id'])\n",
    "off_train.index=np.arange(0,len(off_train),1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#转换优惠率\n",
    "combine=[off_train,test,ftest]\n",
    "def Discount_rate(x):#转化函数\n",
    "    if x.startswith('0'):\n",
    "        return float(x)\n",
    "    else:\n",
    "        return float(x.split(':')[0])/float(x.split(':')[1])\n",
    "for data in combine:\n",
    "    data.index=np.arange(0,len(data),1)\n",
    "    data['Discount_rate']=data['Discount_rate'].apply(Discount_rate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#填充Distance列的空缺值\n",
    "strr=off_train[off_train['Distance']!='null']\n",
    "null=off_train[off_train['Distance']=='null']\n",
    "strr.index=np.arange(0,len(strr),1)\n",
    "null.index=np.arange(0,len(null),1)\n",
    "strr['Distance']=strr['Distance'].apply(lambda x: int(x))\n",
    "print(strr.info())\n",
    "for uid in set(null['User_id'].values):\n",
    "    tt=strr[strr['User_id']==uid]\n",
    "    if len(tt)==0:\n",
    "        null.loc[null[null['User_id']==uid].index,'Distance']=0\n",
    "    else:\n",
    "        null.loc[null[null['User_id']==uid].index,'Distance']=tt['Distance'].median()\n",
    "off_train=pd.concat([strr,null],axis=0)\n",
    "off_train.index=np.arange(0,len(off_train),1)\n",
    "\n",
    "for i in ftest[ftest['Distance']=='null'].index:\n",
    "    ftest.loc[i,'Distance']=int(off_train[(off_train['User_id']==ftest.loc[i,'User_id'])&(off_train['Merchant_id']==ftest.loc[i,'Merchant_id'])]['Distance'].max())\n",
    "    \n",
    "#off_train\\test\\ftest\n",
    "for uid in (test[test['Distance']=='null']['User_id'].values):\n",
    "    if uid in off_train['User_id'].values:\n",
    "        test.loc[test[(test['Distance']=='null')&(test['User_id']==uid)].index,\\\n",
    "                 'Distance']=off_train[off_train['User_id']==uid]['Distance'].median()\n",
    "    else:\n",
    "        test.loc[test[(test['Distance']=='null')&(test['User_id']==uid)].index,'Distance']=0\n",
    "\n",
    "combine=[off_train,test,ftest]\n",
    "for data in combine:\n",
    "    data['Distance']=data['Distance'].apply(lambda x: int(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User_id :  test  76309\n",
      "off_train  510698\n",
      "72149\n",
      ". 10 %:.. 20 %:... 30 %:.... 40 %:..... 50 %:...... 60 %:....... 70 %:........ 80 %:......... 90 %:Merchant_id :  test  1559\n",
      "off_train  5599\n",
      "1474\n",
      ". 10 %:.. 20 %:... 30 %:.... 40 %:..... 50 %:...... 60 %:....... 70 %:........ 80 %:......... 90 %:"
     ]
    }
   ],
   "source": [
    "#找相同uid或mid的行并输出\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "off_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_off_train.csv')\n",
    "test=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_test.csv')\n",
    "ftest=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_ftest.csv')\n",
    "cols=['User_id','Merchant_id']\n",
    "def set_same(col):\n",
    "    b=set(test[col].values)\n",
    "    c=set(off_train[col].values)\n",
    "    print(col,': ','test ',len(b))\n",
    "    print('off_train ',len(c))\n",
    "    print(len(b&c))\n",
    "    return (b&c)\n",
    "\n",
    "#保留off_train中的和test的User_id或mid一样的行\n",
    "row=[]\n",
    "for col in cols:\n",
    "    i=0\n",
    "    j=10\n",
    "    sett=set_same(col)\n",
    "    length=len(sett)\n",
    "    for inde in (sett):\n",
    "        rate=int(i*100/length)\n",
    "        if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "            print('.'*int(rate/10),rate,'%',end=':')\n",
    "            j=j+10\n",
    "        row.extend(list(off_train[off_train[col]==inde].index))\n",
    "        i+=1\n",
    "    \n",
    "row=list(set(row))\n",
    "temp=off_train.loc[row]\n",
    "\n",
    "temp=temp.drop(list(temp[temp['Date']==1].index),axis=0)\n",
    "date=off_train[off_train['Date']==1]\n",
    "off_train_same_umid=pd.concat([temp,date],axis=0)\n",
    "off_train_same_umid=off_train_same_umid.sort_values(by=['User_id'])\n",
    "off_train_same_umid.index=np.arange(0,len(off_train_same_umid),1)\n",
    "off_train_same_umid.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\sameumid\\off_train_same_umid.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 其中discount_rate代表商品实际的价格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "off_train_same_umid=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\sameumid\\off_train_same_umid.csv')\n",
    "test=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_test.csv')\n",
    "ftest=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_ftest.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "      <th>ord_buy</th>\n",
       "      <th>same_merc</th>\n",
       "      <th>Coupon_month_num</th>\n",
       "      <th>month</th>\n",
       "      <th>discount_rate</th>\n",
       "      <th>Courate_month_l6</th>\n",
       "      <th>Courate_month_m6</th>\n",
       "      <th>Coupon_month_getl30</th>\n",
       "      <th>Coupon_month_getm30</th>\n",
       "      <th>Coupre_month_usepro</th>\n",
       "      <th>Coupon_premonth_usepro</th>\n",
       "      <th>Coupon_premonth_get</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1433</td>\n",
       "      <td>8735</td>\n",
       "      <td>6.00</td>\n",
       "      <td>10</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>11525</td>\n",
       "      <td>2</td>\n",
       "      <td>30.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.005083</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>1469</td>\n",
       "      <td>2902</td>\n",
       "      <td>0.95</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3102</td>\n",
       "      <td>6</td>\n",
       "      <td>0.95</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.028666</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>36</td>\n",
       "      <td>1041</td>\n",
       "      <td>13490</td>\n",
       "      <td>6.00</td>\n",
       "      <td>4</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6815</td>\n",
       "      <td>1</td>\n",
       "      <td>30.00</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.035070</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>36</td>\n",
       "      <td>5717</td>\n",
       "      <td>12349</td>\n",
       "      <td>4.00</td>\n",
       "      <td>8</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4921</td>\n",
       "      <td>1</td>\n",
       "      <td>20.00</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>144</td>\n",
       "      <td>1553</td>\n",
       "      <td>10027</td>\n",
       "      <td>0.95</td>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>0.95</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>215</td>\n",
       "      <td>129</td>\n",
       "      <td>8944</td>\n",
       "      <td>6.00</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>319</td>\n",
       "      <td>5</td>\n",
       "      <td>30.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>236</td>\n",
       "      <td>1041</td>\n",
       "      <td>13490</td>\n",
       "      <td>6.00</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6815</td>\n",
       "      <td>1</td>\n",
       "      <td>30.00</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.035070</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>236</td>\n",
       "      <td>1433</td>\n",
       "      <td>8735</td>\n",
       "      <td>6.00</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>11525</td>\n",
       "      <td>2</td>\n",
       "      <td>30.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.005083</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>236</td>\n",
       "      <td>5717</td>\n",
       "      <td>12349</td>\n",
       "      <td>4.00</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4921</td>\n",
       "      <td>1</td>\n",
       "      <td>20.00</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>238</td>\n",
       "      <td>760</td>\n",
       "      <td>2418</td>\n",
       "      <td>6.00</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>29285</td>\n",
       "      <td>6</td>\n",
       "      <td>30.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.390655</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id  Coupon_id  Discount_rate  Distance  Date_received  \\\n",
       "0        4         1433       8735           6.00        10             14   \n",
       "1        4         1469       2902           0.95        10              7   \n",
       "2       36         1041      13490           6.00         4             25   \n",
       "3       36         5717      12349           4.00         8             25   \n",
       "4      144         1553      10027           0.95         1             27   \n",
       "5      215          129       8944           6.00         1             24   \n",
       "6      236         1041      13490           6.00         0             25   \n",
       "7      236         1433       8735           6.00         0             11   \n",
       "8      236         5717      12349           4.00         0             25   \n",
       "9      238          760       2418           6.00         4              8   \n",
       "\n",
       "   Date  ord_buy  same_merc  Coupon_month_num  month  discount_rate  \\\n",
       "0     0        0          1             11525      2          30.00   \n",
       "1     0        0          1              3102      6           0.95   \n",
       "2     0        0          1              6815      1          30.00   \n",
       "3     0        0          1              4921      1          20.00   \n",
       "4     0        0          1                19      2           0.95   \n",
       "5     0        1          1               319      5          30.00   \n",
       "6     0        0          1              6815      1          30.00   \n",
       "7     0        0          1             11525      2          30.00   \n",
       "8     0        0          1              4921      1          20.00   \n",
       "9     0        0          1             29285      6          30.00   \n",
       "\n",
       "   Courate_month_l6  Courate_month_m6  Coupon_month_getl30  \\\n",
       "0                 1                 0                    1   \n",
       "1                 1                 0                    1   \n",
       "2                 2                 0                    2   \n",
       "3                 2                 0                    2   \n",
       "4                 1                 0                    1   \n",
       "5                 1                 0                    1   \n",
       "6                 2                 0                    2   \n",
       "7                 1                 0                    1   \n",
       "8                 2                 0                    2   \n",
       "9                 1                 0                    1   \n",
       "\n",
       "   Coupon_month_getm30  Coupre_month_usepro  Coupon_premonth_usepro  \\\n",
       "0                    0             0.005083                     0.0   \n",
       "1                    0             0.028666                     0.0   \n",
       "2                    0             0.035070                     0.0   \n",
       "3                    0             0.000000                     0.0   \n",
       "4                    0             0.050000                     0.0   \n",
       "5                    0             0.000000                     0.0   \n",
       "6                    0             0.035070                     0.0   \n",
       "7                    0             0.005083                     0.0   \n",
       "8                    0             0.000000                     0.0   \n",
       "9                    0             0.390655                     0.0   \n",
       "\n",
       "   Coupon_premonth_get  \n",
       "0                  0.0  \n",
       "1                  0.0  \n",
       "2                  0.0  \n",
       "3                  0.0  \n",
       "4                  0.0  \n",
       "5                  0.0  \n",
       "6                  2.0  \n",
       "7                  2.0  \n",
       "8                  2.0  \n",
       "9                  0.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "off_train_same_umid.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for i in set(off_train_same_umid['discount_rate'].values):\n",
    "    print(i,len(off_train_same_umid[off_train_same_umid['discount_rate']==i]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ". 10 %:.. 20 %:... 30 %:.... 40 %:..... 50 %:...... 60 %:....... 70 %:........ 80 %:......... 90 %:"
     ]
    }
   ],
   "source": [
    "#每月领取的优惠券张数\n",
    "combine=[off_train_same_umid]\n",
    "for data in combine:\n",
    "    i=0\n",
    "    j=10\n",
    "    length=len(set(data['User_id'].values))\n",
    "    \n",
    "    data['Courate_month_l6']=0              #每月领取优惠券大于Discount_rate6的张数\n",
    "    data['Courate_month_m6']=0              #每月领取优惠券小于等于Discount_rate6的张数\n",
    "    data['Coupon_month_getl30']=0            #每月领取优惠券小于等于discount_rate30的张数\n",
    "    data['Coupon_month_getm30']=0           #每月领取优惠券大于discount_rate30的张数\n",
    "    for uid in set(data['User_id'].values):\n",
    "        rate=int(i*100/length)\n",
    "        if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "            print('.'*int(rate/10),rate,'%',end=':')\n",
    "            j=j+10\n",
    "            \n",
    "        tt=data[data['User_id']==uid]\n",
    "        for month in np.arange(1,7,1):\n",
    "            tat=tt[tt['month']==month]\n",
    "            if len(tat)==0:\n",
    "                continue\n",
    "            else:\n",
    "                data.loc[tat.index,'Courate_month_l6']=len(tat[tat['Discount_rate']<=6])\n",
    "                data.loc[tat.index,'Courate_month_m6']=len(tat[tat['Discount_rate']>6])\n",
    "                data.loc[tat.index,'Coupon_month_getl30']=len(tat[tat['discount_rate']<=30])\n",
    "                data.loc[tat.index,'Coupon_month_getm30']=len(tat[tat['discount_rate']>30])\n",
    "        i=i+1\n",
    "\n",
    "combine=[test,ftest]\n",
    "for data in combine:\n",
    "    data['Courate_month_l6']=0              #每月领取优惠券大于Discount_rate6的张数\n",
    "    data['Courate_month_m6']=0              #每月领取优惠券小于等于Discount_rate6的张数\n",
    "    data['Coupon_month_getl30']=0            #每月领取优惠券小于等于discount_rate30的张数\n",
    "    data['Coupon_month_getm30']=0           #每月领取优惠券大于discount_rate30的张数\n",
    "    for uid in set(data['User_id'].values):\n",
    "        tt=data[data['User_id']==uid]\n",
    "        data.loc[tt.index,'Courate_month_l6']=len(tt[tt['Discount_rate']<=6])\n",
    "        data.loc[tt.index,'Courate_month_m6']=len(tt[tt['Discount_rate']>6])\n",
    "        data.loc[tt.index,'Coupon_month_getl30']=len(tt[tt['discount_rate']<=30])\n",
    "        data.loc[tt.index,'Coupon_month_getm30']=len(tt[tt['discount_rate']>30])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ". 10 %:.. 20 %:... 30 %:.... 40 %:..... 50 %:...... 60 %:....... 70 %:........ 80 %:......... 90 %:. 10 %:.. 20 %:... 30 %:.... 40 %:..... 50 %:...... 60 %:....... 70 %:........ 80 %:......... 90 %:"
     ]
    }
   ],
   "source": [
    "off_train_same_umid['Coupon_month_num']=0 #该优惠券本月发放的数目\n",
    "off_train_same_umid['Coupre_month_usepro']=0#该优惠券上个月使用的概率\n",
    "i=0\n",
    "j=10\n",
    "length=len(set(off_train_same_umid['Merchant_id'].values))\n",
    "for mid in set(off_train_same_umid['Merchant_id'].values):\n",
    "    rate=int(i*100/length)\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%',end=':')\n",
    "        j=j+10\n",
    "            \n",
    "    use=mid\n",
    "    for month in np.arange(1,7,1):\n",
    "        tt=off_train_same_umid[(off_train_same_umid['Merchant_id']==use)&(off_train_same_umid['month']==month)]\n",
    "        if len(tt)!=0:\n",
    "            off_train_same_umid.loc[tt.index,'Coupon_month_num']=len(tt)\n",
    "            prett=off_train_same_umid[(off_train_same_umid['Merchant_id']==use)&(off_train_same_umid['month']==(month-1))]\n",
    "            if len(prett)!=0:\n",
    "                off_train_same_umid.loc[tt.index,'Coupre_month_usepro']=len(prett[prett['Date']==1])/len(prett)\n",
    "    i=i+1\n",
    "\n",
    "for mid in set(off_train_same_umid[off_train_same_umid['month']==1]['Merchant_id'].values):   #填充一月份的数据\n",
    "    tt=off_train_same_umid[(off_train_same_umid['month']==1)&(off_train_same_umid['Merchant_id']==mid)]\n",
    "    tat=off_train_same_umid[(off_train_same_umid['month']!=1)&(off_train_same_umid['Merchant_id']==mid)]\n",
    "    if len(tat)!=0:\n",
    "        off_train_same_umid.loc[tt.index,'Coupre_month_usepro']=tat['Coupre_month_usepro'].median()\n",
    "\n",
    "combine=[ftest,test]\n",
    "i=5\n",
    "for data in combine:\n",
    "    data['Coupon_month_num']=0 #该优惠券本月发放的数目\n",
    "    data['Coupre_month_usepro']=0#该优惠券上个月使用的概率\n",
    "    val=off_train_same_umid['Merchant_id'].values\n",
    "    for mid in set(data['Merchant_id'].values):\n",
    "        tt=data[data['Merchant_id']==mid]\n",
    "        data.loc[tt.index,'Coupon_month_num']=len(tt)\n",
    "        if mid in val:\n",
    "            tat=off_train_same_umid[(off_train_same_umid['Merchant_id']==mid)&(off_train_same_umid['month']==i)]\n",
    "            if len(tat)!=0:\n",
    "                data.loc[tt.index,'Coupre_month_usepro']=len(tat[tat['Date']==1])/len(tat)\n",
    "    i=6\n",
    "#----------------------------------------------------------------------------------------------------------------------------------------------\n",
    "off_train_same_umid['Coupon_premonth_usepro']=0  #用户上个月使用的概率\n",
    "off_train_same_umid['Coupon_premonth_get']=0  #用户上个月领取的张数\n",
    "i=0\n",
    "j=10\n",
    "length=len(set(off_train_same_umid['User_id'].values))\n",
    "for uid in set(off_train_same_umid['User_id'].values):\n",
    "    rate=int(i*100/length)\n",
    "    if (rate in list(np.arange(10.,120.,10.)))&(rate==j):\n",
    "        print('.'*int(rate/10),rate,'%',end=':')\n",
    "        j=j+10\n",
    "    \n",
    "    for month in np.arange(2,7,1):\n",
    "        tt=off_train_same_umid[(off_train_same_umid['User_id']==uid)&(off_train_same_umid['month']==month)]\n",
    "        tat=off_train_same_umid[(off_train_same_umid['User_id']==uid)&(off_train_same_umid['month']==(month-1))]\n",
    "        if len(tt)!=0:\n",
    "            off_train_same_umid.loc[tt.index,'Coupon_premonth_get']=len(tat)\n",
    "            if len(tat)!=0:\n",
    "                off_train_same_umid.loc[tt.index,'Coupon_premonth_usepro']=len(tat[tat['Date']==1])/len(tat)\n",
    "    i=i+1\n",
    "\n",
    "for uid in set(off_train_same_umid[off_train_same_umid['month']==1]['User_id'].values):   #填充一月份的数据\n",
    "    tt=off_train_same_umid[(off_train_same_umid['month']==1)&(off_train_same_umid['User_id']==uid)]\n",
    "    tat=off_train_same_umid[(off_train_same_umid['month']!=1)&(off_train_same_umid['User_id']==uid)]\n",
    "    if len(tat)!=0:\n",
    "        off_train_same_umid.loc[tt.index,'Coupon_premonth_get']=tat['Coupon_premonth_get'].median()\n",
    "        off_train_same_umid.loc[tt.index,'Coupon_premonth_usepro']=tat['Coupon_premonth_usepro'].median()\n",
    "\n",
    "combine=[ftest,test]\n",
    "i=5\n",
    "for data in combine:\n",
    "    data['Coupon_premonth_usepro']=0  #用户上个月使用的概率\n",
    "    data['Coupon_premonth_get']=0  #用户上个月领取的张数\n",
    "    val=off_train_same_umid['User_id'].values\n",
    "    for uid in set(data['User_id'].values):\n",
    "        tt=data[data['User_id']==uid]\n",
    "        if uid in val:\n",
    "            tat=off_train_same_umid[(off_train_same_umid['User_id']==uid)&(off_train_same_umid['month']==i)]\n",
    "            data.loc[tt.index,'Coupon_premonth_get']=len(tat)\n",
    "            if len(tat)!=0:\n",
    "                data.loc[tt.index,'Coupre_month_usepro']=len(tat[tat['Date']==1])/len(tat)\n",
    "    i=6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "off_train_same_umid.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\sameumid\\off_train_same_umid.csv',index=False)\n",
    "test.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_test.csv',index=False)\n",
    "ftest.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_ftest.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "off_train_same_umid=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\sameumid\\off_train_same_umid.csv')\n",
    "test=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_test.csv')\n",
    "ftest=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_ftest.csv')\n",
    "# on_train=pd.read_csv(r'D:\\Data\\TCForNewComer\\ccf_online_stage1_train\\ccf_online_stage1_train.csv')\n",
    "# on_train[on_train[on_train['Coupon_id']=='fixed'].index,'Coupon_id']='10'\n",
    "\n",
    "# col=['online_buy','online_cou','low_price','click']\n",
    "# combine=[off_train_same_umid,test,ftest]\n",
    "# for data in combine:\n",
    "#     for i in col:\n",
    "#         data[i]=0\n",
    "\n",
    "# for data in combine:\n",
    "#     for uid in set(data['User_id'].values):\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "set(off_train_same_umid['Coupon_premonth_get'].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "566752\n",
      "64395\n",
      "len: 428692\n",
      "User_id :  76309\n",
      "312451\n",
      "70531\n",
      "194390\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "off_train_same_umid=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\sameumid\\off_train_same_umid.csv')\n",
    "test=pd.read_csv(r'D:\\Data\\TCForNewComer\\new_script\\train_test.csv')\n",
    "#正负样本取样\n",
    "def set_same(fir,sec,col):\n",
    "    b=set(fir[col].values)\n",
    "    c=set(sec[col].values)\n",
    "    print(col,': ',len(b))\n",
    "    print(len(c))\n",
    "    print(len(c&b))\n",
    "    return (c&b)\n",
    "posi=off_train_same_umid[off_train_same_umid['Date']==1]\n",
    "negt=off_train_same_umid[off_train_same_umid['Date']==0]\n",
    "# negt=negt.drop_duplicates(subset=['Merchant_id','Coupon_id','Discount_rate','Distance','Date','Date_received','month'])\n",
    "print(len(negt))\n",
    "print(len(posi))\n",
    "negt=negt.drop_duplicates(subset=['User_id','discount_rate'])\n",
    "# negt=negt.drop_duplicates(subset=['Merchant_id','Coupon_id','Distance'])\n",
    "print('len:',len(negt))\n",
    "negt.index=np.arange(0,len(negt),1)\n",
    "index1=[]\n",
    "for uid in set_same(test,negt,'User_id'):\n",
    "    for mid in set_same(test,negt,'Merchant_id'):\n",
    "    index1.extend(negt[negt['User_id']==uid].index)\n",
    "temp1=negt.loc[index1]\n",
    "posi=pd.concat([posi,temp1],axis=0)\n",
    "posi.index=np.arange(0,len(posi),1)\n",
    "print(len(posi))\n",
    "negt.drop(index1,axis=0,inplace=True)\n",
    "negt.index=np.arange(0,len(negt),1)      \n",
    "posi.to_csv(r'D:\\Data\\TCForNewComer\\new_script\\sameumid\\sampling.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "posi=off_train_same_umid[off_train_same_umid['Date']==1]\n",
    "negt=off_train_same_umid[off_train_same_umid['Date']==0]\n",
    "# negt=negt.drop_duplicates(subset=['User_id','Discount_rate','Date'])\n",
    "negt=negt.drop_duplicates(subset=['Merchant_id','Coupon_id','Discount_rate','Distance','Date','Date_received','month'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "298697"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(negt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "194390"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(posi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "      <th>ord_buy</th>\n",
       "      <th>same_merc</th>\n",
       "      <th>Coupon_month_num</th>\n",
       "      <th>month</th>\n",
       "      <th>discount_rate</th>\n",
       "      <th>Courate_month_l6</th>\n",
       "      <th>Courate_month_m6</th>\n",
       "      <th>Coupon_month_getl30</th>\n",
       "      <th>Coupon_month_getm30</th>\n",
       "      <th>Coupre_month_usepro</th>\n",
       "      <th>Coupon_premonth_usepro</th>\n",
       "      <th>Coupon_premonth_get</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>947</td>\n",
       "      <td>3939</td>\n",
       "      <td>1110</td>\n",
       "      <td>20.0</td>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1434</td>\n",
       "      <td>5</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.181818</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>947</td>\n",
       "      <td>6610</td>\n",
       "      <td>12941</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>44</td>\n",
       "      <td>5</td>\n",
       "      <td>150.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>947</td>\n",
       "      <td>8506</td>\n",
       "      <td>12414</td>\n",
       "      <td>20.0</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>200.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    User_id  Merchant_id  Coupon_id  Discount_rate  Distance  Date_received  \\\n",
       "40      947         3939       1110           20.0        10             31   \n",
       "41      947         6610      12941            5.0         7              6   \n",
       "42      947         8506      12414           20.0         7             10   \n",
       "\n",
       "    Date  ord_buy  same_merc  Coupon_month_num  month  discount_rate  \\\n",
       "40     0        0          1              1434      5          100.0   \n",
       "41     0        1          1                44      5          150.0   \n",
       "42     0        1          2                 5      6          200.0   \n",
       "\n",
       "    Courate_month_l6  Courate_month_m6  Coupon_month_getl30  \\\n",
       "40                 1                 1                    0   \n",
       "41                 1                 1                    0   \n",
       "42                 0                 2                    0   \n",
       "\n",
       "    Coupon_month_getm30  Coupre_month_usepro  Coupon_premonth_usepro  \\\n",
       "40                    2             0.181818                     0.0   \n",
       "41                    2             0.000000                     0.0   \n",
       "42                    2             0.333333                     0.0   \n",
       "\n",
       "    Coupon_premonth_get  \n",
       "40                  0.0  \n",
       "41                  0.0  \n",
       "42                  2.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "negt[negt['User_id']==947]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "posi.sort_values(by=['User_id','Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
